package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IPersonExtBeginDao;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.PersonExt;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Repository
public class PersonExtBeginDaoImpl extends BaseDaoImpl implements IPersonExtBeginDao {
    @Override
    public void batchSavePersonExtBegin(List<PersonExt> personExts) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        List<Object[]> paramList = new ArrayList<>(personExts.size());
        for(PersonExt personExt : personExts){
            Object[] paramArr = {
                    personExt.getId(), personExt.getCreateTime(), personExt.getUpdateTime(), personExt.getPersonId(),
                    personExt.getPersonAddress(), personExt.getAgencyId(), personExt.getDeptId(), personExt.getAgencyCode(),
                    personExt.getIdenNo(),personExt.getState(),//20210304新增
                    personExt.getEnterAgencyDate(), personExt.getRetireDate(), personExt.getPersonTypeCode(),
                    personExt.getPersonIdeCode(), personExt.getEndDate(), personExt.getDeadDate(),personExt.getRemarks(),
                    personExt.getSalaCardNo(), personExt.getSalaCardBank(), personExt.getGovCredCardNo(),
                    personExt.getGovCredCardBank(), personExt.getStartDate(), personExt.getMeritPayCode(),
                    personExt.getSchRecCode(), personExt.getPosCode(), personExt.getGrCode(), personExt.getTecGrCode(),
                    personExt.getIsAuth(), personExt.getSalaGrCode(), personExt.getPersonState(), personExt.getChangeState(),
                    personExt.getVerifyState(), personExt.getPerSourceCode(), personExt.getIsSpecialPost(), personExt.getIsInsurance(),
                    personExt.getIsUniSala(), personExt.getIsStopSalary(), personExt.getIsDeleted(),personExt.getMofDivCode(),
                    personExt.getPersonTypeId(), personExt.getPostId(), personExt.getGradeId(), personExt.getLevelId(),personExt.getPersonName(),
                    personExt.getShowOrder(),personExt.getPhone(),personExt.getPersonnelCode(),
                    personExt.getFuncId(),personExt.getFuncName(),personExt.getFuncCode()
            };
            List<Object> params = new ArrayList<>(Arrays.asList(paramArr));

            paramList.add(params.toArray());
        }
        StringBuilder sql = new StringBuilder();
        sql.append("MERGE into ").append(TableName.SAL_PERSON_EXT_BEGIN);
        sql.append(" t using (select ? s.id,? s.create_time,? s.update_time , ? s.person_id,? s.person_address")
                .append("? s.agency_id,? s.dept_id,? s.agency_code,? s.iden_no,")
                .append(" ? s.state,? s.enter_agency_date,? s.retire_date,? s.person_type_code,? s.person_ide_code,? s.end_date,? s.dead_date,")
                .append(" ? s.remarks,? s.sala_card_no,? s.sala_card_bank,? s.gov_cred_card_no,? s.gov_cred_card_bank,? s.start_date,? s.merit_pay_code,? s.sch_rec_code,")
                .append(" ? s.pos_code,? s.gr_code,? s.tec_gr_code,? s.is_auth,? s.sala_gr_code,? s.person_state,? s.change_state,? s.verify_state ")
                .append(" ? s.per_source_code,? s.is_special_post,? s.is_insurance,? s.is_uni_sala,? s.is_stop_salary,? s.is_deleted,? s.mof_div_code,? s.person_type_id ")
                .append( " ? s.post_id,? s.grade_id,? s.level_id,? s.person_name,? s.show_order,? s.phone,? s.personnel_code,? s.func_id  ,? s.func_name, ? s.func_code")
                .append("from sal_salary_veryify ) s on (t.id=s.id)")
                .append("when matched then update set " )
                .append(" (t.id= s.id ,t.create_time = s.create_time,t.update_time=s.update_time ,t.person_id= s.person_id,t.person_address= s.person_address,")
                .append("t.agency_id=s.agency_id,t.dept_id = s.dept_id,t.agency_code= s.agency_code,t.iden_no= s.iden_no ,")
                .append("t.state=s.state,t.enter_agency_date= s.enter_agency_date,t.retire_date= s.retire_date ,t.person_type_code= s.person_type_code,")
                .append("t.person_ide_code= s.person_ide_code,t.end_date= s.end_date,t.dead_date= s.dead_date,")
                .append("t.remarks=s.remarks,t.sala_card_no= s.sala_card_no,t.sala_card_bank= s.sala_card_bank ,t.gov_cred_card_no= s.gov_cred_card_no,")
                .append("t.gov_cred_card_bank= s.gov_cred_card_bank,t.start_date= s.start_date,t.merit_pay_code= s.merit_pay_code,t.sch_rec_code = s.sch_rec_code,")
                .append("t.pos_code =s.pos_code ,t.gr_code= s.gr_code ,t.tec_gr_code= s.tec_gr_code,t.is_auth = s.is_auth ,t.sala_gr_code= s.sala_gr_code,")
                .append("t.person_state= s.person_state,t.change_state = s.change_state,t.verify_state = s.verify_state")
                .append("t.per_source_code=s.per_source_code,t.is_special_post= s.is_special_post,t.is_insurance= s.is_insurance,t.is_uni_sala = s.is_uni_sala ,")
                .append("t.is_stop_salary = s.is_stop_salary ,t.is_deleted= s.is_deleted,t.mof_div_code= s.mof_div_code,t.person_type_id= s.person_type_id")
                .append("t.post_id=s.post_id ,t.grade_id= s.grade_id ,t.level_id= s.level_id ,t.person_name= s.person_name,t.show_order= s.show_order,")
                .append("t.phone= s.phone,t.personnel_code= s.personnel_code,t.func_id= s.func_id ,t.func_name=s.func_name, t.func_code= s.func_code)	")
                .append(" when not matched then insert values " )
                .append("(s.id, s.create_time,s.update_time ,  s.person_id, s.person_address")
                .append("s.agency_id, s.dept_id, s.agency_code, s.iden_no,")
                .append("s.state, s.enter_agency_date, s.retire_date, s.person_type_code, s.person_ide_code, s.end_date, s.dead_date,")
                .append("s.remarks, s.sala_card_no, s.sala_card_bank, s.gov_cred_card_no, s.gov_cred_card_bank, s.start_date, s.merit_pay_code, s.sch_rec_code,")
                .append("s.pos_code, s.gr_code, s.tec_gr_code, s.is_auth, s.sala_gr_code, s.person_state, s.change_state, s.verify_state ")
                .append("s.per_source_code, s.is_special_post, s.is_insurance, s.is_uni_sala, s.is_stop_salary, s.is_deleted, s.mof_div_code, s.person_type_id ")
                .append("s.post_id, s.grade_id, s.level_id, s.person_name, s.show_order, s.phone, s.personnel_code, s.func_id  , s.func_name,  s.func_code)");




        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < 50; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void batchPhysicalDeletePersonExtBegin(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.SAL_PERSON_EXT_BEGIN)
                .append(" where ").append(" mof_div_code = ? ");
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        }
        super.getjdbcTemplate().update(sb.toString(), province);
    }

    @Override
    public List<PersonExt> queryPersonExt(String mofDivCode,List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        if(!CollectionUtils.isEmpty(agencyIds)) {
            sql.append("select id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
                    .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                    .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                    .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                    .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary,mof_div_code,is_deleted," )
                    .append("person_name,show_order,phone,personnel_code,func_id,func_name,func_code ");
            sql.append(" from ").append(TableName.SAL_PERSON_EXT_BEGIN)
                    .append(" where is_deleted=0  ").append(" and ").append(SysUtils.inSql("agency_id", agencyIds))
                    .append(" and mof_div_code=? ");
            sql.append(" order by create_time");
            Object[] param = {mofDivCode};
            //请求参数初始化
            return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(PersonExt.class),param);
        }
        return new ArrayList<>();
    }

    @Override
    public void updatePersonExtBeginCode(List<AgencyVerify> updateList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.SAL_PERSON_EXT_BEGIN);
        sql.append(" set  agency_code=?   where agency_id=?");
        List<Object[]> paramsList = new ArrayList<>(updateList.size());
        for (AgencyVerify personExtBegin : updateList) {
            Object[] params = new Object[]{
                    personExtBegin.getAgencyCode(),personExtBegin.getAgencyId()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
}
